package com.myebills.ebpsfrontend.cust.maxis;

import java.sql.*;
import java.io.*;
import java.util.*;
import com.myebills.ebpsfrontend.cust.controller.*;
import com.checkfree.isolutions.property.Properties;
import com.checkfree.isolutions.property.PropertiesConfig;


public class PhoneBookDAO {
	private static Connection connection = null;
	private static PhoneBookDAO instance = null;

	private PhoneBookDAO() {
	}

	private Connection getConnection(){
            try{
                //Fixme
                connection = EbppxCusDBUtil.getDbConnection();
                /*******erase this*********
		System.out.println("conn trying..");
                Driver driver = (Driver)
                    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
                DriverManager.registerDriver(driver);
		connection = DriverManager.getConnection("jdbc:oracle:thin:@172.16.16.129:1521:EBP100U1","cfadmin","cfadmin");
                *******erase this*********/
            } catch (Exception e) {
              	System.out.println(e.toString());
	    }

            return connection;
	}

	public static PhoneBookDAO getInstance() {
		//thread safe implementation - double-check idiom
		if (instance == null) {
			synchronized (PhoneBookDAO.class) {
				if (instance == null) {
					instance = new PhoneBookDAO();
				}
			}
		}
		return instance;
	}

	public void delete(String userid, String number) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "DELETE FROM custphonebook WHERE userid=? and phoneno=?";
                PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;

		try {
		    	//for(int x=0; x<numbers.length; x++){
			        pstmt = connection.prepareStatement(sql);
			        pstmt.setString(1, userid);
			        pstmt.setString(2, number);
			        int i = pstmt.executeUpdate();
			//}

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //update phonebook

	public void updateAll(String userid, String[] numbers,String[] names,String[] callTypes) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "UPDATE custphonebook SET name=?, calltype=? WHERE userid=? and phoneno=?";
                PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;

		try {
		    	for(int x=0; x<numbers.length; x++){
			        pstmt = connection.prepareStatement(sql);
					pstmt.setString(1, names[x]);
					pstmt.setString(2, callTypes[x]);
					pstmt.setString(3, userid);
					pstmt.setString(4, numbers[x]);
			        int i = pstmt.executeUpdate();
			}

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //update phonebook

	public void update(String userid, String number, String name) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "UPDATE custphonebook SET name=? WHERE userid=? and phoneno=?";
                PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;

		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, userid);
			pstmt.setString(3, number);
			int i = pstmt.executeUpdate();

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //update phonebook

	public boolean update(String userid, String number, String name, String callType) throws Exception {
		Connection connection = instance.getConnection();
		boolean boolResult = false;

		String sql = "UPDATE custphonebook SET name=?, calltype=? WHERE userid=? and phoneno=?";

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;

		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, callType);
			pstmt.setString(3, userid);
			pstmt.setString(4, number);
			int i = pstmt.executeUpdate();

			if(i>0){
				boolResult = true;
			}

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();
			}
		}
		return boolResult;
	}  //update phonebook

	public Collection getContact(String userid, String number) throws Exception {
		Connection connection = instance.getConnection();

                Statement stmt = null;
		ResultSet rs = null;
		Collection resultList = null;
		boolean exactFound = false;
                HashMap map = new HashMap();



		try {
		    	//find exact match first
                        String sql = "SELECT * FROM custphonebook WHERE userid='" +userid+ "' AND phoneno='" +number+ "'";
			stmt = connection.createStatement();
      		        rs = stmt.executeQuery(sql);

                        resultList = new ArrayList();
                        while(rs.next()){
                                 String phoneno = rs.getString("phoneno");
                                 String name = rs.getString("name");
                                 String callType = rs.getString("calltype");
                                 PhoneBook pb = new PhoneBook();
                                 pb.setNumber(phoneno);
                                 pb.setName(name);
                                 pb.setCallType(callType);
                                 resultList.add(pb);
                                 exactFound = true;
                        }

                        //find submatch if no exact match
                        if ((exactFound==false)&&(number.length()>=7)){

                                 sql = "SELECT * FROM custphonebook WHERE userid='" +userid+ "'";
			         stmt = connection.createStatement();
      		                 rs = stmt.executeQuery(sql);

      		                 // Added by Meau for new phone book
      		                 while(rs.next()){
								 String phoneno = rs.getString("phoneno");
								 String name = rs.getString("name");
								 String callType = rs.getString("calltype");

                                 boolean subMatch = number.endsWith(phoneno);
                                 if (subMatch==false){
									 subMatch = phoneno.endsWith(number);
								 }

								 if (subMatch==true){
									 PhoneBook pb = new PhoneBook();
									 pb.setNumber(phoneno);
									 pb.setName(name);
									 pb.setCallType(callType);
									 resultList.add(pb);
								} //if


							 }
      		                 //end added by Meau
                        }

                        return resultList;

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //list phonebook

	public Collection list(String userid) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "SELECT * FROM custphonebook WHERE userid=? ORDER BY UPPER(NAME)";
                PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;

		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userid);
			rs = pstmt.executeQuery();

                        resultList = new ArrayList();
                        while(rs.next()){
                                 String phoneno = rs.getString("phoneno");
                                 String name = rs.getString("name");
                                 String callType = rs.getString("calltype");
                                 PhoneBook pb = new PhoneBook();
                                 pb.setNumber(phoneno);
                                 pb.setName(name);
                                 pb.setCallType(callType);
                                 resultList.add(pb);
                        }

                        return resultList;

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //list phonebook

	public PhoneBook listByNo(String userid,String phoneNo) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "SELECT * FROM custphonebook WHERE userid=? and phoneNo=? ORDER BY UPPER(NAME)";
        PreparedStatement pstmt = null;
		ResultSet rs = null;
		PhoneBook pb = new PhoneBook();

		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userid);
			pstmt.setString(2, phoneNo);
			rs = pstmt.executeQuery();

			if(rs.next()){
					 String phoneno = rs.getString("phoneno");
					 String name = rs.getString("name");
					 String callType = rs.getString("calltype");
					 pb.setNumber(phoneno);
					 pb.setName(name);
					 pb.setCallType(callType);
			}
		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
		return pb;
	}  //list phonebook


	public Collection list(String userid,String[] numbers) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "SELECT * FROM custphonebook WHERE userid=? and phoneno=? ";
                PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = resultList = new ArrayList();

		try {
			for(int i=0;i<numbers.length;i++){
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, userid);
				pstmt.setString(2, numbers[i]);
				rs = pstmt.executeQuery();

				while(rs.next()){
				 String phoneno = rs.getString("phoneno");
				 String name = rs.getString("name");
				 String callType = rs.getString("calltype");
				 PhoneBook pb = new PhoneBook();
				 pb.setNumber(phoneno);
				 pb.setName(name);
				 pb.setCallType(callType);
				 resultList.add(pb);
				}

			}
			return resultList;

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //list phonebook


	public Map list(String userid,String callType) throws Exception {
		Connection connection = instance.getConnection();

		String sql = "SELECT phoneno,name FROM custphonebook WHERE userid=? and calltype=? ";
                PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;
		Map phoneBookMap = new HashMap();

		Properties appCfg = PropertiesConfig.getApplicationSettings();
		String confDir = "";

		if (appCfg.hasProperty("PropertyFileDir")) {
			confDir =  appCfg.getProperty("PropertyFileDir");
		}


		String filename = "PhoneBookCalculation";

		//Start reading the file to be process
		FileInputStream fis = new FileInputStream(confDir + "/" + filename + ".property");
		java.util.Properties p = new java.util.Properties();
		p.load(fis);
		fis.close();

		try {
			String section = p.getProperty("SECTION").trim(); //Separator for Title and Info

			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userid);
			pstmt.setString(2, callType);

			rs = pstmt.executeQuery();

			resultList = new ArrayList();

			while(rs.next()){
				String phoneno = rs.getString("phoneno");
				String name = rs.getString("name");
				PhoneBook pb = new PhoneBook();
				pb.setNumber(phoneno);
				pb.setName(name);
				pb.setCallType(callType);
				resultList.add(pb);
				phoneBookMap.put(phoneno,resultList);


				StringTokenizer sectionSTK = new StringTokenizer(section,"|");

				for(int i=0;i<sectionSTK.countTokens();i++){
					while(sectionSTK.hasMoreTokens()){
						String length = sectionSTK.nextToken();

						if(Integer.parseInt(length)==phoneno.length()){
							String subSection = "SECTION_"+length;

							StringTokenizer subSectionSTK = new StringTokenizer(p.getProperty(subSection),"|");

							boolean boolAction = true;
							for(int j=0;j<subSectionSTK.countTokens();j++){
								while(subSectionSTK.hasMoreTokens()){
									String childSection = subSectionSTK.nextToken();

									StringTokenizer childStrz = new StringTokenizer(childSection,"|");

									while(childStrz.hasMoreTokens()){
										String childHeader = childStrz.nextToken();
										String tempPhoneno = "";

										if(phoneno.startsWith(childHeader)){
											String subSectionAction = subSection+"_ACTION_"+childHeader ;
											String subSectionValue = subSection+"_VALUE_"+childHeader ;

											if(boolAction && p.getProperty(subSectionAction).equals("REMOVE")){
												int intSubSectionValue = Integer.parseInt(p.getProperty(subSectionValue));
												tempPhoneno = phoneno.substring(1);
												pb.setNumber(tempPhoneno);
												pb.setName(name);
												pb.setCallType(callType);
												resultList.add(pb);
												phoneBookMap.put(tempPhoneno,resultList);

												boolAction = false;
											}else if(boolAction && p.getProperty(subSectionAction).equals("ADD")){
												//int intSubSectionValue = Integer.parseInt(p.getProperty(subSectionValue));
												tempPhoneno = p.getProperty(subSectionValue)+phoneno;
												pb.setNumber(tempPhoneno);
												pb.setName(name);
												pb.setCallType(callType);
												resultList.add(pb);
												phoneBookMap.put(tempPhoneno,resultList);
												boolAction = false;
											}
										}
									}
								}
							}
						}

					}


				}


					//find the the section
					//do the action(add/minus 6)
					// add to the phoneMap


				// end if need to add/minus 6

			}
			return phoneBookMap;

		} catch (SQLException e) {
			throw e;
		} finally {

			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();

			}
		}
	}  //list phonebook

	public boolean add(String userid, String number, String name) throws Exception {
		Connection connection = instance.getConnection();

        //check to see if it already exist
		String sql = "SELECT * FROM custphonebook WHERE userid=? and phoneno=?";

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;

		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userid);
			pstmt.setString(2, number);

			rs = pstmt.executeQuery();


            if(rs.next()){  //if record already exist with same userid and phone number
            	return false;
			}else {
				//insert if doesn't exist
				sql = "INSERT INTO custphonebook VALUES (?, ?, ?)";
				pstmt = null;
				resultList = null;

				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, userid);
				pstmt.setString(2, number);
				pstmt.setString(3, name);

				int i = pstmt.executeUpdate();

				return true; // recor inserted
			}
		} catch (SQLException e) {
		        throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();
			}
		} //finally
	}//add phonebook

	public boolean add(String userid, String number, String name, String callType) throws Exception {
		Connection connection = instance.getConnection();

		//check to see if it already exist
		String sql = "SELECT * FROM custphonebook WHERE userid=? and phoneno=?";

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Collection resultList = null;
		boolean boolResult = false;

		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, userid);
			pstmt.setString(2, number);

			rs = pstmt.executeQuery();

			if(rs.next()){  //if record already exist with same userid and phone number
				boolResult = false;
			}else {
				//insert if doesn't exist
				sql = "INSERT INTO custphonebook VALUES (?, ?, ?, ?)";

				pstmt = null;
				resultList = null;

				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, userid);
			   	pstmt.setString(2, number);
			    pstmt.setString(3, name);
			    pstmt.setString(4, callType);

			    int i = pstmt.executeUpdate();

			    boolResult = true; // recor inserted
			}
		} catch (SQLException e) {
		        throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (connection != null) {
				EbppxCusDBUtil.freeDbConnection(connection);
				//connection.close();
			}
		} //finally
		return boolResult;
	}//add phonebook

}

